package com.ajiao5.blob;

import com.util.JDBCUtil;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 方法一：
 * 使用preparedStatement实现批量操作
 * update、delete本身就具有批量操作的效果
 */
public class InsertTest {
    @Test
    public void testInsert() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();

            conn = JDBCUtil.getConnection();
            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
                ps.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.CloseResource(conn, ps);
        }
    }

    /**
     * 方法二：
     * addBatch()\executeBatch();\clearBatch();
     * mysql服务器默认关闭批处理的，我们需要通过一个参数，让mysql开启批量处理的支持
     *    ?rewriteBatchedStatements=true  写在配置文件的url后面
     */
    @Test
    public void testInsert2() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();

            conn = JDBCUtil.getConnection();
            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
                //1."攒"sql   相当于 500的缓冲容量满后 执行
                ps.addBatch();
                if (i % 500 == 0) {
                    //2.执行batch
                    ps.executeBatch();
                    //3.情况batch
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.CloseResource(conn, ps);
        }
    }

    /**
     * 方法三：  最后的优化  缓冲到500 后不允许提交 直到所有的都缓冲满了再一次性提交
     * addBatch()\executeBatch();\clearBatch();
     * mysql服务器默认关闭批处理的，我们需要通过一个参数，让mysql开启批量处理的支持
     *    ?rewriteBatchedStatements=true  写在配置文件的url后面
     */
    @Test
    public void testInsert3() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();

            conn = JDBCUtil.getConnection();
            //设置不允许自动提交数据
            conn.setAutoCommit(false);
            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < 20000; i++) {
                ps.setObject(1, "name_" + i);
                //1."攒"sql   相当于 500的缓冲容量满后 执行
                ps.addBatch();
                if (i % 500 == 0) {
                    //2.执行batch
                    ps.executeBatch();
                    //3.情况batch
                    ps.clearBatch();
                }
            }
            //统一提交数据
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.CloseResource(conn, ps);
        }
    }
}
